﻿using System.Data.SqlClient;
using ReagentStore.ERM;
using ReagentStore.Resources.Properties;

namespace ReagentStore.UTL
{
	public class DataMaintain
	{
		private const string cmdBackup = @"BACKUP DATABASE ReagentStore TO DISK = '{0}' WITH FORMAT";
		private const string cmdRestore = @"USE Master;RESTORE DATABASE ReagentStore FROM DISK = '{0}' WITH REPLACE";

		public static void Backup(string file)
		{
			using (var conn = new SqlConnection(new ConnectionStringBuilder().MaintainConnectionString()))
			{
				conn.Open();
				string text = string.Format(cmdBackup, file);
				var cmd = new SqlCommand(text, conn);
				cmd.ExecuteNonQuery();
			}
		}

		public static void Restore(string file)
		{
			using (var conn = new SqlConnection(new ConnectionStringBuilder().MaintainConnectionString()))
			{
				conn.Open();
				KillActiveConnection();

				string text = string.Format(cmdRestore, file);
				var cmd = new SqlCommand(text, conn);
				cmd.ExecuteNonQuery();
			}
		}

		public static void Reset()
		{
			using (var conn = new SqlConnection(new ConnectionStringBuilder().MaintainConnectionString()))
			{
				conn.Open();
				KillActiveConnection();

				string text = DefaultResources.scriptSql;
				var cmd = new SqlCommand(text, conn);
				cmd.ExecuteNonQuery();
			}
		}

		private static void KillActiveConnection()
		{
			using (var conn = new SqlConnection(new ConnectionStringBuilder().MaintainConnectionString()))
			{
				conn.Open();

				string text = @"SELECT spid FROM sysprocesses WHERE dbid=db_id('ReagentStore')";
				var cmd = new SqlCommand(text, conn);

				SqlDataReader reader = cmd.ExecuteReader();
				var killer = new SqlCommand {Connection = conn};
				while (reader.Read())
				{
					text = string.Format("KILL {0}", reader["spid"]);
					killer.CommandText = text;
					killer.ExecuteNonQuery();
				}

				reader.Close();
			}
		}
	}
}